package com.library.book.dao;

import com.library.book.model.Book;
import com.library.util.JdbcUtil;
import com.library.util.PageModel;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class BookDaoImpl implements BookDao {
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    @Override
    public int addBook(Book book) {
        String sql = "INSERT INTO books (isbn, title, author, publisher, publish_date, price, category, status, createtime, updatetime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";
        return JdbcUtil.jdbcByUpdate(sql, 
                book.getIsbn(), 
                book.getTitle(), 
                book.getAuthor(), 
                book.getPublisher(), 
                book.getPublishDate() != null ? sdf.format(book.getPublishDate()) : null, 
                book.getPrice(), 
                book.getCategory(), 
                book.getStatus());
    }

    @Override
    public int deleteBookById(Integer id) {
        String sql = "DELETE FROM books WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, id);
    }

    @Override
    public int updateBook(Book book) {
        String sql = "UPDATE books SET isbn = ?, title = ?, author = ?, publisher = ?, publish_date = ?, price = ?, category = ?, status = ?, updatetime = NOW() WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, 
                book.getIsbn(), 
                book.getTitle(), 
                book.getAuthor(), 
                book.getPublisher(), 
                book.getPublishDate() != null ? sdf.format(book.getPublishDate()) : null, 
                book.getPrice(), 
                book.getCategory(), 
                book.getStatus(), 
                book.getId());
    }

    @Override
    public Book getBookById(Integer id) {
        String sql = "SELECT * FROM books WHERE id = ?";
        List<Map<String, Object>> result = JdbcUtil.jdbcBySelect(sql, id);
        if (result != null && !result.isEmpty()) {
            return mapToBook(result.get(0));
        }
        return null;
    }

    @Override
    public int updateBookStatus(Integer id, String status) {
        String sql = "UPDATE books SET status = ?, updatetime = NOW() WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, status, id);
    }
    
    @Override
    public List<Map<String, Object>> getBooksByPage(PageModel pageModel) {
        // 获取查询条件
        String likeValue = pageModel.getLikeValue() == null ? "" : pageModel.getLikeValue();
        
        // 构建SQL查询语句
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM books ");
        sql.append("WHERE id LIKE ? OR isbn LIKE ? OR title LIKE ? OR author LIKE ? OR category LIKE ? OR status LIKE ? ");
        
        // 计算分页起始位置
        int startIndex = (pageModel.getPageNo() - 1) * pageModel.getPageSize();
        
        // 添加排序和分页子句
        sql.append("ORDER BY id DESC LIMIT ?, ?");
        
        // 执行查询
        List<Object> params = new ArrayList<>();
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add(startIndex);
        params.add(pageModel.getPageSize());
        
        return JdbcUtil.jdbcBySelect(sql.toString(), params.toArray());
    }
    
    @Override
    public int getBooksCount(PageModel pageModel) {
        // 获取查询条件
        String likeValue = pageModel.getLikeValue() == null ? "" : pageModel.getLikeValue();
        
        // 构建统计SQL查询语句
        StringBuilder countSql = new StringBuilder();
        countSql.append("SELECT COUNT(*) as total FROM books ");
        countSql.append("WHERE id LIKE ? OR isbn LIKE ? OR title LIKE ? OR author LIKE ? OR category LIKE ? OR status LIKE ?");
        
        // 执行统计查询
        List<Map<String, Object>> countResult = JdbcUtil.jdbcBySelect(
            countSql.toString(), 
            "%" + likeValue + "%", 
            "%" + likeValue + "%", 
            "%" + likeValue + "%", 
            "%" + likeValue + "%",
            "%" + likeValue + "%",
            "%" + likeValue + "%"
        );
        
        if (countResult != null && !countResult.isEmpty()) {
            return Integer.parseInt(countResult.get(0).get("total").toString());
        }
        
        return 0;
    }

    // 辅助方法：将Map转换为Book对象
    private Book mapToBook(Map<String, Object> map) {
        Book book = new Book();
        book.setId((Integer) map.get("id"));
        book.setIsbn((String) map.get("isbn"));
        book.setTitle((String) map.get("title"));
        book.setAuthor((String) map.get("author"));
        book.setPublisher((String) map.get("publisher"));
        
        // 处理publish_date
        Object publishDateObj = map.get("publish_date");
        if (publishDateObj != null) {
            if (publishDateObj instanceof Date) {
                book.setPublishDate((Date) publishDateObj);
            } else if (publishDateObj instanceof java.sql.Date) {
                book.setPublishDate(new Date(((java.sql.Date) publishDateObj).getTime()));
            } else if (publishDateObj instanceof Timestamp) {
                book.setPublishDate(new Date(((Timestamp) publishDateObj).getTime()));
            } else if (publishDateObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) publishDateObj;
                book.setPublishDate(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        book.setPrice((Integer) map.get("price"));
        book.setCategory((String) map.get("category"));
        book.setStatus((String) map.get("status"));
        
        // 处理createtime
        Object createTimeObj = map.get("createtime");
        if (createTimeObj != null) {
            if (createTimeObj instanceof Date) {
                book.setCreatetime((Date) createTimeObj);
            } else if (createTimeObj instanceof Timestamp) {
                book.setCreatetime(new Date(((Timestamp) createTimeObj).getTime()));
            } else if (createTimeObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) createTimeObj;
                book.setCreatetime(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        // 处理updatetime
        Object updateTimeObj = map.get("updatetime");
        if (updateTimeObj != null) {
            if (updateTimeObj instanceof Date) {
                book.setUpdatetime((Date) updateTimeObj);
            } else if (updateTimeObj instanceof Timestamp) {
                book.setUpdatetime(new Date(((Timestamp) updateTimeObj).getTime()));
            } else if (updateTimeObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) updateTimeObj;
                book.setUpdatetime(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        return book;
    }
}
